import xarray as xr
import pandas as pd
import hvplot.pandas
import hvplot.xarray
combined_data_file = "combined_positions.csv"
combined_data_file_depth = "combined_positions_depth.csv"
etopo1_baltic = xr.open_dataset("data/ETOPO1_Bed_g_gmt4_baltic.nc")
df = pd.read_csv(combined_data_file)
df
| Timestamp | Longitude | Latitude | Longitude_ | Latitude_ | Platform | Type | |
|---|---|---|---|---|---|---|---|
| 0 | 2023-04-24 00:55:07+00:00 | 10.18159 | 54.32806 | 10.20000 | 54.30000 | Littorina | Vessel |
| 1 | 2023-04-24 01:55:10+00:00 | 10.18163 | 54.32806 | 10.20000 | 54.30000 | Littorina | Vessel |
| 2 | 2023-04-24 02:55:14+00:00 | 10.18160 | 54.32805 | 10.20000 | 54.30000 | Littorina | Vessel |
| 3 | 2023-04-24 03:58:09+00:00 | 10.18160 | 54.32809 | 10.20000 | 54.30000 | Littorina | Vessel |
| 4 | 2023-04-24 04:58:10+00:00 | 10.18161 | 54.32804 | 10.20000 | 54.30000 | Littorina | Vessel |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 13749 | 2023-05-09 08:40:47+00:00 | 10.73319 | 54.93622 | 10.73319 | 54.93622 | D303 | Buoy |
| 13750 | 2023-05-09 08:45:46+00:00 | 10.73306 | 54.93580 | 10.73306 | 54.93580 | D303 | Buoy |
| 13751 | 2023-05-09 09:20:47+00:00 | 10.73313 | 54.93616 | 10.73313 | 54.93616 | D303 | Buoy |
| 13752 | 2023-05-09 09:25:48+00:00 | 10.73306 | 54.93605 | 10.73306 | 54.93605 | D303 | Buoy |
| 13753 | 2023-05-09 09:40:44+00:00 | 10.73317 | 54.93613 | 10.73317 | 54.93613 | D303 | Buoy |
13754 rows × 7 columns
df["depth"] = - etopo1_baltic.z.sel(
x=df[["Longitude"]].to_xarray()["Longitude"],
y=df[["Latitude"]].to_xarray()["Latitude"],
method="nearest",
).to_dataframe()["z"]
df
| Timestamp | Longitude | Latitude | Longitude_ | Latitude_ | Platform | Type | depth | |
|---|---|---|---|---|---|---|---|---|
| 0 | 2023-04-24 00:55:07+00:00 | 10.18159 | 54.32806 | 10.20000 | 54.30000 | Littorina | Vessel | -17.0 |
| 1 | 2023-04-24 01:55:10+00:00 | 10.18163 | 54.32806 | 10.20000 | 54.30000 | Littorina | Vessel | -17.0 |
| 2 | 2023-04-24 02:55:14+00:00 | 10.18160 | 54.32805 | 10.20000 | 54.30000 | Littorina | Vessel | -17.0 |
| 3 | 2023-04-24 03:58:09+00:00 | 10.18160 | 54.32809 | 10.20000 | 54.30000 | Littorina | Vessel | -17.0 |
| 4 | 2023-04-24 04:58:10+00:00 | 10.18161 | 54.32804 | 10.20000 | 54.30000 | Littorina | Vessel | -17.0 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 13749 | 2023-05-09 08:40:47+00:00 | 10.73319 | 54.93622 | 10.73319 | 54.93622 | D303 | Buoy | -3.0 |
| 13750 | 2023-05-09 08:45:46+00:00 | 10.73306 | 54.93580 | 10.73306 | 54.93580 | D303 | Buoy | -3.0 |
| 13751 | 2023-05-09 09:20:47+00:00 | 10.73313 | 54.93616 | 10.73313 | 54.93616 | D303 | Buoy | -3.0 |
| 13752 | 2023-05-09 09:25:48+00:00 | 10.73306 | 54.93605 | 10.73306 | 54.93605 | D303 | Buoy | -3.0 |
| 13753 | 2023-05-09 09:40:44+00:00 | 10.73317 | 54.93613 | 10.73317 | 54.93613 | D303 | Buoy | -3.0 |
13754 rows × 8 columns
df.to_csv(combined_data_file_depth, index=False)
!head -n5 {combined_data_file_depth}
Timestamp,Longitude,Latitude,Longitude_,Latitude_,Platform,Type,depth 2023-04-24 00:55:07+00:00,10.18159,54.32806,10.2,54.3,Littorina,Vessel,-17.0 2023-04-24 01:55:10+00:00,10.18163,54.32806,10.2,54.3,Littorina,Vessel,-17.0 2023-04-24 02:55:14+00:00,10.1816,54.32805,10.2,54.3,Littorina,Vessel,-17.0 2023-04-24 03:58:09+00:00,10.1816,54.32809,10.2,54.3,Littorina,Vessel,-17.0